package com.cms.biz.help.impl;

import java.sql.ResultSet;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cms.biz.dict.CMSContractServiceMgr;
import com.cms.biz.help.CMSDictHelpServiceMgr;
import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.framework.sys.business.AbstractBusinessObjectServiceMgr;
import com.metadata.biz.MetaDataServiceMgr;

@Transactional(rollbackFor = Exception.class)
@Service("CMSDictHelpServiceMgr")
public class CMSDictHelpServiceMgrImpl extends AbstractBusinessObjectServiceMgr implements CMSDictHelpServiceMgr {
	
	@Autowired
	private         JdbcTemplate            jdbcTemplate;
	
	@Autowired
	private   MetaDataServiceMgr           dictMeataData;
	
	public EFDataSet getHTXMDataSetRow(String keywords) {
		return getHTXMDataSetRow(keywords, "F_XMMC like '" + keywords + "%' and F_SYZT = '1'");
	}
	
	public EFDataSet getHTXMDataSetRow(String keywords, String BILL_WHERE) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance("HTXXZD");
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			strSql = "select HTXXZD.*, HYZGZD.F_ZGMC as F_XMFZRMC from HTXXZD "
				   + " left join HYZGZD on HTXXZD.F_XMFZR = HYZGZD.F_ZGBH"
				   + " where " + BILL_WHERE + " order by F_HTBH desc";
			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return dataset;
	}
	
	public EFDataSet getSCTZHTXMDataSetRow(String keywords) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance("HTXXZD");
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			strSql = "select * from HTXXZD where F_XMMC like '" + keywords + "%' and F_SYZT = '1'"
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.F_CHAR01 = HTXXZD.F_HTBH and FLOW_ID = 'SCTZ_FLOW')"
			       + " order by F_HTBH";
			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return dataset;
	}
	
	public EFDataSet getWXTZHTXMDataSetRow(String keywords) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance("HTXXZD");
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			strSql = "select * from HTXXZD where F_XMMC like '" + keywords + "%' and F_SYZT = '1'"
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.F_CHAR01 = HTXXZD.F_HTBH and FLOW_ID = 'WXTZ_FLOW')"
			       + " order by F_HTBH";
			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return dataset;
	}
}
